package com.aaa.dao.impl;

import com.aaa.dao.BaseDao;
import com.aaa.dao.CourseDao;
import com.aaa.entity.CourseEntity;
import com.aaa.entity.Pager;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class CourseDaoImpl extends BaseDao implements CourseDao {

    /**
     * 分页查询课程
     * @param pageNo
     * @param pageSize
     * @return
     */
    @Override
    public Pager queryByPages(int pageNo, int pageSize, Map condation) {
        //动态拼接判断
        String sql="select *from (select *from (select * from course c JOIN (select u1.user_id,u1.user_name  from (select u.user_id,u.user_name,ur.role_id from sys_user u JOIN sys_user_role ur WHERE u.user_id=ur.user_id ) u1 join sys_role r  WHERE u1.role_id=r.role_id  and r.role_id=10) tt where c.teacher=tt.user_id) c1 left JOIN  course_type ct on c1.type_id=ct.id) c1 where 1=1 and del_flag=0 ";
        //创建StringBuffer对象实现动态拼接
        StringBuffer buffer=new StringBuffer(sql);
        //条件： 课程名称  费用  班级状态  课时
        //参数集合
        List params=new ArrayList();
        if (condation!=null){
            if (condation.containsKey("course_name")&&condation.get("course_name").toString().length()>0){
                buffer.append(" and course_name like ?");
                params.add("%"+condation.get("course_name")+"%");
            }
            if (condation.containsKey("minCharge")&&condation.get("minCharge").toString().length()>0&&condation.containsKey("maxCharge")&&condation.get("maxCharge").toString().length()>0){
                buffer.append(" and charge between ? and ?");
                params.add(condation.get("minCharge"));
                params.add(condation.get("maxCharge"));
            }

            if (condation.containsKey("status")&&condation.get("status").toString().length()>0){
                    buffer.append("and state = ?");
                    params.add(condation.get("status"));
            }
            if (condation.containsKey("user_id")&&condation.get("user_id").toString().length()>0){
                buffer.append("and user_id = ?");
                params.add(condation.get("user_id"));
            }
            if (condation.containsKey("type_id")&&condation.get("type_id").toString().length()>0){
                buffer.append("and type_id = ?");
                params.add(condation.get("type_id"));
            }

            if (condation.containsKey("minHour")&&condation.get("minHour").toString().length()>0&&condation.containsKey("maxHour")&&condation.get("maxHour").toString().length()>0){
                buffer.append(" and hour  between ? and ?");
                params.add(condation.get("minHour"));
                params.add(condation.get("maxHour"));
            }

        }
           //查询总条数
        sql="select count(*) c from ("+buffer+") course1";
        Map map=  this.executeQuery(sql,params.toArray()).get(0);
        Long totalCount= (Long) map.get("c");

        //拼接分页的限定
        buffer.append(" limit ?,?");
        int startIndex=(pageNo-1)*pageSize;
        params.add(startIndex);
        params.add(pageSize);

        List list=this.executeQuery(buffer.toString(),params.toArray());

        Pager pager=new Pager(pageNo,pageSize,list,totalCount.intValue());
        return pager;
    }

    /**
     * 查询单个
     * @param course_id
     * @return
     */
    @Override
    public Map queryOne(int course_id) {
        String sql="select *from course where course_id=?";
        List<Map> map=this.executeQuery(sql,course_id);
        return map.size()>0?map.get(0):null;
    }

    /**
     * 添加
     * @param course
     * @return
     */
    @Override
    public int course_add(CourseEntity course,int teacher) {
        String sql="insert into course (type_id,course_name,charge,state,hour,teacher)" +
                "values(?,?,?,0,?,?)";
        return this.executeUpdate(sql,course.getTypeId(),course.getCourseName(),course.getCharge(),course.getHour(),teacher);
    }

    /**
     * 修改
     * @param course
     * @return
     */
    @Override
    public int course_update(CourseEntity course,int teacher) {
        String sql="update course set type_id=?,course_name=?,charge=?,hour=?,teacher=? where course_id=?";
        return this.executeUpdate(sql,course.getTypeId(),course.getCourseName(),course.getCharge(),course.getHour(),teacher,course.getCourseId());
    }

    /**
     * 删除
     * @param course_id
     * @return
     */
    @Override
    public int course_delete(int course_id) {
        String sql="update course set del_flag=1 where course_id=?";
        return this.executeUpdate(sql,course_id);
    }

    @Override
    public List<Map> query_course() {
        String sql="select*from course where del_flag=0";
        return this.executeQuery(sql);
    }


    /**
     * 查询课程类型
     * @return
     */
    @Override
    public List queryType() {
        String sql="select id,type_name from course_type where del=0 ";
        return this.executeQuery(sql);
    }

    @Override
    public int updateState(int state ,int course_id ) {
        String sql="update course set state=? where course_id=?";
        return this.executeUpdate(sql,state,course_id);
    }

    @Override
    public List queryTeacher() {
        String sql="select u1.user_id,u1.user_name  from (select u.user_id,u.user_name,ur.role_id from sys_user u JOIN sys_user_role ur WHERE u.user_id=ur.user_id ) u1 join sys_role r  WHERE u1.role_id=r.role_id  and r.role_id=10 ";
        return this.executeQuery(sql);
    }


}
